package cn.emma.json;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.sling.commons.json.JSONArray;
import org.apache.sling.commons.json.JSONObject;

import java.io.FileInputStream;
import java.io.FileWriter;
import java.util.HashMap;
import java.util.Map;

public class XlsxtoJson {
	public static void main(String[] args) {
        FileInputStream inputStream = null;
        FileWriter writer = null;
        try{
            inputStream = new FileInputStream("C:\\Users\\emma\\123.xlsx");
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            writer = new FileWriter("C:\\Users\\emma\\test2.json");
            JSONObject jsonData = transferXlsxToJson(workbook);
            jsonData.write(writer);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (inputStream!=null){
                try {
                    inputStream.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
            if (writer != null){
                try{
                    writer.flush();
                    writer.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    private static JSONObject transferXlsxToJson (XSSFWorkbook workbook) throws Exception{
        //获取sheet数目，判断excel是否可用
        int sheetCount = workbook.getNumberOfSheets();
        if (sheetCount < 1) {
            throw new Exception ("Not any available sheet");
        }
        //获取第一个sheet对象
        XSSFSheet sheet = workbook.getSheetAt(0);
        //获取Upper列的值
        XSSFRow tRow = sheet.createRow(1);//索引是从0开始

        //记录索引位置
        Map<String, Integer> indexMap = new HashMap<>();
        for (Cell cell: tRow) {
            String cellValue = cell.getStringCellValue();
            if (!(cellValue == null)|| cellValue==""){
                indexMap.put(cellValue, cell.getColumnIndex());
            }
        }

        int lastRowNum = sheet.getLastRowNum();
        JSONArray dataArr = new JSONArray();

        for (int rowIndex = 3; rowIndex <= lastRowNum; rowIndex++) {
            String incomeThreholdUpper = "";
            XSSFRow xRow = sheet.getRow(rowIndex);
            XSSFCell upperCell =  xRow.getCell(1);
      
            if (upperCell.getCellType()==0){
                incomeThreholdUpper = String.valueOf(Math.round(upperCell.getNumericCellValue()));
            }else if(upperCell.getCellType()==1){
                if (upperCell.getStringCellValue().equals("+")){
                    incomeThreholdUpper = "infinity";
                }
            }
            //获取Couple下所有列的值
            int lastCellNum = xRow.getLastCellNum();
            JSONArray CoupleArr = new JSONArray();
            int dependents = 0;
            for (int cellIndex = indexMap.get("Couple"); cellIndex <indexMap.get("Single") ; cellIndex++) {
                XSSFCell xCell = xRow.getCell(cellIndex);                
                JSONObject jsonObj = new JSONObject().accumulate("dependents", dependents).accumulate("value", Math.round(xCell.getNumericCellValue()));
                CoupleArr.put(jsonObj);
                dependents++;
            }
            JSONObject CoupleObj = new JSONObject().accumulate("type","Couple").accumulate("this type", CoupleArr);

            //
            JSONArray SingleArr = new JSONArray();
            dependents = 0;
            for (int cellIndex = indexMap.get("Single"); cellIndex <lastCellNum ; cellIndex++) {
                XSSFCell xCell = xRow.getCell(cellIndex);
                JSONObject jsonObj = new JSONObject().accumulate("dependents", dependents).accumulate("value", Math.round(xCell.getNumericCellValue()));
                SingleArr.put(jsonObj);
                dependents++;
            }
            JSONObject SingleObj = new JSONObject().accumulate("type", "Single").accumulate("thisType", SingleArr);
            JSONObject rowObj = new JSONObject().accumulate("incomeThreholdUpper",incomeThreholdUpper).accumulate("thisIncomeLevel", new JSONArray().put(SingleObj).put(CoupleObj));
            dataArr.put(rowObj);
        }
        JSONObject Table = new JSONObject().accumulate("config", new JSONObject().accumulate("currency","$").accumulate("data", dataArr));
        return Table;
    }

}
   

